{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Hackathon 3.x Data Preparation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Libraries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Load data:\n",
    "train = pd.read_csv('Train_nyOWmfK.csv')\n",
    "test = pd.read_csv('Test_bCtAN1w.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((87020, 26), (37717, 24))"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape, test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                        object\n",
       "Gender                    object\n",
       "City                      object\n",
       "Monthly_Income             int64\n",
       "DOB                       object\n",
       "Lead_Creation_Date        object\n",
       "Loan_Amount_Applied      float64\n",
       "Loan_Tenure_Applied      float64\n",
       "Existing_EMI             float64\n",
       "Employer_Name             object\n",
       "Salary_Account            object\n",
       "Mobile_Verified           object\n",
       "Var5                       int64\n",
       "Var1                      object\n",
       "Loan_Amount_Submitted    float64\n",
       "Loan_Tenure_Submitted    float64\n",
       "Interest_Rate            float64\n",
       "Processing_Fee           float64\n",
       "EMI_Loan_Submitted       float64\n",
       "Filled_Form               object\n",
       "Device_Type               object\n",
       "Var2                      object\n",
       "Source                    object\n",
       "Var4                       int64\n",
       "LoggedIn                   int64\n",
       "Disbursed                  int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124737, 27)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Combine into data:\n",
    "train['source']= 'train'\n",
    "test['source'] = 'test'\n",
    "data=pd.concat([train, test],ignore_index=True)\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check missing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "City                      1401\n",
       "DOB                          0\n",
       "Device_Type                  0\n",
       "Disbursed                37717\n",
       "EMI_Loan_Submitted       84901\n",
       "Employer_Name              113\n",
       "Existing_EMI               111\n",
       "Filled_Form                  0\n",
       "Gender                       0\n",
       "ID                           0\n",
       "Interest_Rate            84901\n",
       "Lead_Creation_Date           0\n",
       "Loan_Amount_Applied        111\n",
       "Loan_Amount_Submitted    49535\n",
       "Loan_Tenure_Applied        111\n",
       "Loan_Tenure_Submitted    49535\n",
       "LoggedIn                 37717\n",
       "Mobile_Verified              0\n",
       "Monthly_Income               0\n",
       "Processing_Fee           85346\n",
       "Salary_Account           16801\n",
       "Source                       0\n",
       "Var1                         0\n",
       "Var2                         0\n",
       "Var4                         0\n",
       "Var5                         0\n",
       "source                       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Look at categories of all object variables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Frequency count for variable Gender\n",
      "Male      71398\n",
      "Female    53339\n",
      "Name: Gender, dtype: int64\n",
      "\n",
      "Frequency count for variable Salary_Account\n",
      "HDFC Bank                                          25180\n",
      "ICICI Bank                                         19547\n",
      "State Bank of India                                17110\n",
      "Axis Bank                                          12590\n",
      "Citibank                                            3398\n",
      "Kotak Bank                                          2955\n",
      "IDBI Bank                                           2213\n",
      "Punjab National Bank                                1747\n",
      "Bank of India                                       1713\n",
      "Bank of Baroda                                      1675\n",
      "Standard Chartered Bank                             1434\n",
      "Canara Bank                                         1385\n",
      "Union Bank of India                                 1330\n",
      "Yes Bank                                            1120\n",
      "ING Vysya                                            996\n",
      "Corporation bank                                     948\n",
      "Indian Overseas Bank                                 901\n",
      "State Bank of Hyderabad                              854\n",
      "Indian Bank                                          773\n",
      "Oriental Bank of Commerce                            761\n",
      "IndusInd Bank                                        711\n",
      "Andhra Bank                                          706\n",
      "Central Bank of India                                648\n",
      "Syndicate Bank                                       614\n",
      "Bank of Maharasthra                                  576\n",
      "HSBC                                                 474\n",
      "State Bank of Bikaner & Jaipur                       448\n",
      "Karur Vysya Bank                                     435\n",
      "State Bank of Mysore                                 385\n",
      "Federal Bank                                         377\n",
      "Vijaya Bank                                          354\n",
      "Allahabad Bank                                       345\n",
      "UCO Bank                                             344\n",
      "State Bank of Travancore                             333\n",
      "Karnataka Bank                                       279\n",
      "United Bank of India                                 276\n",
      "Dena Bank                                            268\n",
      "Saraswat Bank                                        265\n",
      "State Bank of Patiala                                263\n",
      "South Indian Bank                                    223\n",
      "Deutsche Bank                                        176\n",
      "Abhyuday Co-op Bank Ltd                              161\n",
      "The Ratnakar Bank Ltd                                113\n",
      "Tamil Nadu Mercantile Bank                           103\n",
      "Punjab & Sind bank                                    84\n",
      "J&K Bank                                              78\n",
      "Lakshmi Vilas bank                                    69\n",
      "Dhanalakshmi Bank Ltd                                 66\n",
      "State Bank of Indore                                  32\n",
      "Catholic Syrian Bank                                  27\n",
      "India Bulls                                           21\n",
      "B N P Paribas                                         15\n",
      "Firstrand Bank Limited                                11\n",
      "GIC Housing Finance Ltd                               10\n",
      "Bank of Rajasthan                                      8\n",
      "Kerala Gramin Bank                                     4\n",
      "Industrial And Commercial Bank Of China Limited        3\n",
      "Ahmedabad Mercantile Cooperative Bank                  1\n",
      "Name: Salary_Account, dtype: int64\n",
      "\n",
      "Frequency count for variable Mobile_Verified\n",
      "Y    80928\n",
      "N    43809\n",
      "Name: Mobile_Verified, dtype: int64\n",
      "\n",
      "Frequency count for variable Var1\n",
      "HBXX    84901\n",
      "HBXC    12952\n",
      "HBXB     6502\n",
      "HAXA     4214\n",
      "HBXA     3042\n",
      "HAXB     2879\n",
      "HBXD     2818\n",
      "HAXC     2171\n",
      "HBXH     1387\n",
      "HCXF      990\n",
      "HAYT      710\n",
      "HAVC      570\n",
      "HAXM      386\n",
      "HCXD      348\n",
      "HCYS      318\n",
      "HVYS      252\n",
      "HAZD      161\n",
      "HCXG      114\n",
      "HAXF       22\n",
      "Name: Var1, dtype: int64\n",
      "\n",
      "Frequency count for variable Filled_Form\n",
      "N    96740\n",
      "Y    27997\n",
      "Name: Filled_Form, dtype: int64\n",
      "\n",
      "Frequency count for variable Device_Type\n",
      "Web-browser    92105\n",
      "Mobile         32632\n",
      "Name: Device_Type, dtype: int64\n",
      "\n",
      "Frequency count for variable Var2\n",
      "B    53481\n",
      "G    47338\n",
      "C    20366\n",
      "E     1855\n",
      "D      918\n",
      "F      770\n",
      "A        9\n",
      "Name: Var2, dtype: int64\n",
      "\n",
      "Frequency count for variable Source\n",
      "S122    55249\n",
      "S133    42900\n",
      "S159     7999\n",
      "S143     6140\n",
      "S127     2804\n",
      "S137     2450\n",
      "S134     1900\n",
      "S161     1109\n",
      "S151     1018\n",
      "S157      929\n",
      "S153      705\n",
      "S144      447\n",
      "S156      432\n",
      "S158      294\n",
      "S123      112\n",
      "S141       83\n",
      "S162       60\n",
      "S124       43\n",
      "S150       19\n",
      "S160       11\n",
      "S136        5\n",
      "S138        5\n",
      "S155        5\n",
      "S139        4\n",
      "S129        4\n",
      "S135        2\n",
      "S131        1\n",
      "S130        1\n",
      "S132        1\n",
      "S125        1\n",
      "S140        1\n",
      "S142        1\n",
      "S126        1\n",
      "S154        1\n",
      "Name: Source, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "var = ['Gender','Salary_Account','Mobile_Verified','Var1','Filled_Form','Device_Type','Var2','Source']\n",
    "for v in var:\n",
    "    print '\\nFrequency count for variable %s'%v\n",
    "    print data[v].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handle Individual Variables:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### City Variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "len(data['City'].unique())\n",
    "#drop city because too many unique\n",
    "data.drop('City',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Determine Age from DOB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    23-May-78\n",
       "1    07-Oct-85\n",
       "2    10-Oct-81\n",
       "3    30-Nov-87\n",
       "4    17-Feb-84\n",
       "Name: DOB, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['DOB'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    37\n",
       "1    30\n",
       "2    34\n",
       "3    28\n",
       "4    31\n",
       "Name: Age, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create age variable:\n",
    "data['Age'] = data['DOB'].apply(lambda x: 115 - int(x[-2:]))\n",
    "data['Age'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#drop DOB:\n",
    "data.drop('DOB',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### EMI_Load_Submitted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x10b270f90>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYcAAAEBCAYAAACT92m7AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAHZ5JREFUeJzt3X+U1fV95/HniyGISZDBtMQtBscEsJg1mRDFtLXrRFfQ\ndqtkt1JyNoGJnO6pSKNtNxuwZzOyOdsEz26dpFnNpiUOWlNC2PojiUVq5Jq6EsBEggQK47aDQJRz\nIjAnbhJW8L1/3M8dv8ydX3dmvnPvZV6Pczh+P5/7+Xzv5+Zk7vv7+bw/3+9VRGBmZpY1odoDMDOz\n2uPgYGZmZRwczMysjIODmZmVcXAwM7MyDg5mZlZm0OAgaZ2ko5J296r/Q0n7JL0g6fOZ+tWSOtNr\nCzL18yTtlnRAUnumfpKkDanPNkkzM68tS+33S1o68o9rZmZDMZSZw/3AwmyFpBbgd4DLIuIy4L+l\n+rnAYmAucANwrySlbvcByyNiDjBHUumcy4FjETEbaAfuTueaBnwGuAK4EmiTNHWYn9PMzCowaHCI\niGeA472qbwU+HxGnUpufpPqbgA0RcSoiuoBOYL6kC4ApEbEztXsAWJTpsz4dbwKuSccLgS0R0R0R\nJ4AtwPUVfj4zMxuG4eYc5gD/StL3JG2V9MFUPwM4lGl3JNXNAA5n6g+nujP6RMRpoFvS+QOcy8zM\ncjZxBP2mRcSHJF0BfAN49yiNSYM3MTOzPA03OBwC/hYgInZKOi3pHRSv7mdm2l2Y6o4A7+qjnsxr\nP5bUAJwXEcckHQFaevXZ2tdgJPkBUWZmwxARfV6QDzU4iDOv6B+hmBt4WtIcYFJEvCrpMeAhSX9O\ncQloFrAjIkJSt6T5wE5gKfDFdK7HgGXAduBm4KlU/wTwX1MSegJwHbBqgA84xI9iNrbuuusu7rrr\nrmoPw6zMm/uFyg0aHCR9jeIV/DskvQS0AV8F7pf0AnCS4pc9EbFX0kZgL/A6sCLe/Na+DegAJgOP\nR8TmVL8OeFBSJ/AqsCSd67ikzwLPAQGsSYlps7rS1dVV7SGYVUxnwxW3pDgbPoednVpbW+no6Kj2\nMMzKSOp3Wcl3SJvlrLW1tdpDMKuYZw5mZuOUZw5mVVQoFKo9BLOKOTiYmVkZLyuZmY1TXlYyM7OK\nODiY5cw5B6tHDg5mZlbGOQczs3HKOQczM6uIg4NZzpxzsHrk4GBmZmWcczAzG6ecczAzs4o4OJjl\nrL29vdpDMKuYg4NZznbt2lXtIZhVzMHBLGdNTU3VHoJZxYb6G9JmVoFCodCzhXXNmjU99S0tLbS0\ntFRnUGYV8G4ls5z5Z0KtVo1ot5KkdZKOStrdx2t/IukNSedn6lZL6pS0T9KCTP08SbslHZDUnqmf\nJGlD6rNN0szMa8tS+/2Sllbyoc3MbPiGknO4H1jYu1LShcB1wMFM3VxgMTAXuAG4V1IpKt0HLI+I\nOcAcSaVzLgeORcRsoB24O51rGvAZ4ArgSqBN0tSKP6FZlfk3pK0eDRocIuIZ4HgfL90DfKpX3U3A\nhog4FRFdQCcwX9IFwJSI2JnaPQAsyvRZn443Adek44XAlojojogTwBbg+iF9KrMa4hyD1aNh7VaS\ndCNwKCJe6PXSDOBQpnwk1c0ADmfqD6e6M/pExGmgOy1T9Xcus7riZytZPap4t5Kkc4E7KS4p5aHP\n5IiZmY2d4WxlfQ/QBPww5RMuBH4gaT7Fq/uZmbYXprojwLv6qCfz2o8lNQDnRcQxSUeAll59tvY3\nqNbW1p795I2NjTQ3N/dM50tXbi67XI1yqa5WxuPy+C0XCoWenXOD3X8zpK2skpqAb0bEZX289s/A\nvIg4LulS4CGKCeQZwN8DsyMiJH0P+CSwE/g28MWI2CxpBfAvI2KFpCXAoohYkhLSzwHzKC5/PQd8\nMOUfeo/BW1nNzCo00q2sXwOepbjD6CVJn+jVJEhLQRGxF9gI7AUeB1ZkvrVvA9YBB4DOiNic6tcB\nvySpE7gDWJXOdRz4LMWgsB1Y01dgMKt1pSs3s3rim+DMcrZy5Uq+9KUvVXsYZmX8yG6zKtqzZ0+1\nh2BWMQcHMzMr4wfvmeWgvb2dRx55BICnn366Z+fIokWLuOOOO6o4MrOhcc7BLGfNzc3+TQerSc45\nmFVRY2NjtYdgVjEHB7OcLVq0aPBGZjXGwcEsZ83NzdUeglnFHBzMzKyME9JmZuOUE9JmZlYRBwez\nnPnZSlaPHBzMzKyMcw5mZuOUcw5mZlYRBweznDnnYPXIwcHMzMo452BmNk4552BmZhVxcDDLmXMO\nVo8GDQ6S1kk6Kml3pu5uSfsk7ZL0vySdl3lttaTO9PqCTP08SbslHZDUnqmfJGlD6rNN0szMa8tS\n+/2Slo7ORzYzs8EMZeZwP7CwV90W4L0R0Qx0AqsBJF0KLAbmAjcA90oqrWfdByyPiDnAHEmlcy4H\njkXEbKAduDudaxrwGeAK4EqgTdLUYX1Ksyoq/QqcWT0ZNDhExDPA8V51T0bEG6n4PeDCdHwjsCEi\nTkVEF8XAMV/SBcCUiNiZ2j0AlB5yfxOwPh1vAq5JxwuBLRHRHREnKAak6yv8fGZmNgyjkXO4BXg8\nHc8ADmVeO5LqZgCHM/WHU90ZfSLiNNAt6fwBzmVWV5xzsHo0cSSdJf0p8HpE/M0ojQegz21Vg2lt\nbaWpqQko/ixjc3Nzz3S+9MfpssvVKJd+P7pWxuPy+C0XCgU6OjoAer4v+zOk+xwkXQR8MyLel6lr\nBX4fuCYiTqa6VUBExNpU3gy0AQeBrRExN9UvAa6OiFtLbSJiu6QG4OWImJ7atETEH6Q+X07n+Hof\n4/N9DmZmFRqN+xxE5ope0vXAp4AbS4EheQxYknYgXQzMAnZExCsUl4vmpwT1UuDRTJ9l6fhm4Kl0\n/ARwnaSpKTl9XaozM7OcDWUr69eAZynuMHpJ0ieAvwDeDvy9pB9IuhcgIvYCG4G9FPMQKzKX9LcB\n64ADQGdEbE7164BfktQJ3AGsSuc6DnwWeA7YDqxJiWmzulKa1pvVEz8+wyxnhUKhZ/3XrJYMtKzk\n4GBmNk752UpmZlYRBweznDnnYPXIwcHMzMo452BmNk4552BmZhVxcDDLmXMOVo8cHMzMrIxzDmZm\n45RzDmZmVhEHB7OcOedg9cjBwczMyjjnYGY2TjnnYGZmFXFwMMuZcw5WjxwczMysjHMOZmbjlHMO\nZmZWEQcHs5w552D1aNDgIGmdpKOSdmfqpknaImm/pCckTc28tlpSp6R9khZk6udJ2i3pgKT2TP0k\nSRtSn22SZmZeW5ba75e0dHQ+spmZDWYoM4f7gYW96lYBT0bEJcBTwGoASZcCi4G5wA3AvZJK61n3\nAcsjYg4wR1LpnMuBYxExG2gH7k7nmgZ8BrgCuBJoywYhs3rR0tJS7SGYVWzQ4BARzwDHe1XfBKxP\nx+uBRen4RmBDRJyKiC6gE5gv6QJgSkTsTO0eyPTJnmsTcE06XghsiYjuiDgBbAGur+CzmZnZMA03\n5zA9Io4CRMQrwPRUPwM4lGl3JNXNAA5n6g+nujP6RMRpoFvS+QOcy6yuOOdg9WjiKJ1nNPeR9rmt\najCtra00NTUB0NjYSHNzc890vvTH6bLL1Sjv2rWrpsbj8vgtFwoFOjo6AHq+L/szpPscJF0EfDMi\n3pfK+4CWiDialoy2RsRcSauAiIi1qd1moA04WGqT6pcAV0fEraU2EbFdUgPwckRMT21aIuIPUp8v\np3N8vY/x+T4Hq1mFQqHnD9WslozGfQ7izCv6x4DWdLwMeDRTvyTtQLoYmAXsSEtP3ZLmpwT10l59\nlqXjmykmuAGeAK6TNDUlp69LdWZ1pXTlZlZPBl1WkvQ1oAV4h6SXKM4EPg98Q9ItFGcFiwEiYq+k\njcBe4HVgReaS/jagA5gMPB4Rm1P9OuBBSZ3Aq8CSdK7jkj4LPEdx2WpNSkyb1ZWurq5qD8GsYn58\nhlkOCoVCz4xhzZo1tLW1AcV1Xy8xWa0YaFlptBLSZpbROwjcddddVRuL2XD48RlmZlbGwcEsZ42N\njdUeglnFHBzMctbc3FztIZhVzAlpM7Nxyr/nYGZmFXFwMMuZb4KzeuTgYJaz0rOVzOqJg4NZzk6c\n8I39Vn8cHMzMrIzvkDbLQe/HZ5T48RlWL7yV1Sxnzc3NzjtYTfJWVrMq8h3SVo+8rGSWg+yy0tNP\nP93z4D0vK1m98LKSWc5aW1t7fprRrJZ4WcnMzCri4GCWs9bW1moPwaxiXlYyMxunvKxkVkV+tpLV\noxEFB0l/JGmPpN2SHpI0SdI0SVsk7Zf0hKSpmfarJXVK2idpQaZ+XjrHAUntmfpJkjakPtskzRzJ\neM3MbGiGHRwk/Qrwh8C8iHgfxW2xHwVWAU9GxCXAU8Dq1P5SYDEwF7gBuFdSaTpzH7A8IuYAcyQt\nTPXLgWMRMRtoB+4e7njNqsVbV60ejXRZqQF4m6SJwLnAEeAmYH16fT2wKB3fCGyIiFMR0QV0AvMl\nXQBMiYidqd0DmT7Zc20Crh3heM3GnJeVrB4NOzhExI+B/w68RDEodEfEk8A7I+JoavMKMD11mQEc\nypziSKqbARzO1B9OdWf0iYjTwAlJ5w93zGbV4HscrB4N+w5pSY0Ur+wvArqBb0j690DvbUOjuY2o\nz6w6FLcLNjU1AcXHFTQ3N/dM50tXbi67XI3yK6+8QqFQqJnxuDx+y4VCoedipfR92Z9hb2WV9LvA\nwoj4/VT+OPAh4BqgJSKOpiWjrRExV9IqICJibWq/GWgDDpbapPolwNURcWupTURsl9QAvBwR0/sY\ni7eyWk0p9Hoqa1tbG+DHZ1htGWgr60ierfQS8CFJk4GTFPMBO4HXgFZgLbAMeDS1fwx4SNI9FJeL\nZgE7IiIkdUuan/ovBb6Y6bMM2A7cTDHBbVbzegeB0rOVzOrFsINDROyQtAl4Hng9/fcrwBRgo6Rb\nKM4KFqf2eyVtBPam9isyl/u3AR3AZODxiNic6tcBD0rqBF4Flgx3vGbV0tXVVe0hmFXMd0ib5ay9\nvZ077rij2sMwKzPQspKDg5nZOOXHZ5iZWUUcHMxyVtq1ZFZPHBzMzKyMcw5mZuOUcw5mZlYRBwez\nnDnnYPXIwcEsZ1/4wheqPQSzijk4mOXs+eefr/YQzCrm4GBmZmUcHMxysHLlSpqammhqauLgwYM9\nxytXrqz20MyGxFtZzXKWtgtWexhmZbyV1czMKuLgYJaDyy67jIkTJzJxYvGp+KXjyy67rMojMxua\nkfzYj5n147XXXuONN97oKZeOX3vttWoNyawizjmY5cw5B6tVzjmYmVlFHBzMzKyMcw5mOWhvb+eR\nRx7pKbe0tACwaNEi/2So1YURzRwkTZX0DUn7JP1I0pWSpknaImm/pCckTc20Xy2pM7VfkKmfJ2m3\npAOS2jP1kyRtSH22SZo5kvGajZUXX3yRrq4uurq6AHqOX3zxxeoOzGyIRpSQltQBPB0R90uaCLwN\nuBN4NSLulvRpYFpErJJ0KfAQcAVwIfAkMDsiQtJ2YGVE7JT0OPCFiHhC0q3AZRGxQtLvAR+JiCV9\njMMJaatZTkhbrcolIS3pPOA3I+J+gIg4FRHdwE3A+tRsPbAoHd8IbEjtuoBOYL6kC4ApEbEztXsg\n0yd7rk3AtcMdr5mZDd1Icg4XAz+RdD/wfuA54A7gnRFxFCAiXpE0PbWfAWzL9D+S6k4BhzP1h1N9\nqc+hdK7Tkk5IOj8ijo1g3Ga5c87B6t1IgsNEYB5wW0Q8J+keYBXQe/48mvPpPqc/AK2trTQ1NQHQ\n2NhIc3Nzzx9k6cdWXHZ5rMrr1q2js7OTkmeffZYJEyZw6tQpmpubqz4+l8dnuVAo0NHRAdDzfdmf\nYeccJL0T2BYR707lqygGh/cALRFxNC0ZbY2IuZJWARERa1P7zUAbcLDUJtUvAa6OiFtLbSJiu6QG\n4OWImN7HWJxzsJpSKBR6/ijXrFlDW1sbUPwDLf3RmlXbQDmHYc8c0pf/IUlzIuIAxXzAj9K/VmAt\nsAx4NHV5DHgozTBmALOAHSkh3S1pPrATWAp8MdNnGbAduBl4arjjNRtLmzZt4lvf+lZPuXS19pOf\n/MTBwerCSO9z+CTFL/y3AP8EfAJoADZKuoXirGAxQETslbQR2Au8DqzIXO7fBnQAk4HHI2Jzql8H\nPCipE3gVKNupZFaLZs2a1TNtL/2eQ6nerB6MKDhExA8pbk3t7V/30/5zwOf6qP8+UPa4yog4SQou\nZmY2dnyHtFkOSjfBlZSOfROc1Qs/ldUsBw0NDWc8srtkwoQJnD59ugojMiuXS0LazPr3ne98p9/d\nSmb1wDMHsxx85CMfYevWrQB0d3czdWrxEWMf/vCHefjhh6s5NLMenjmYjbHbb7+d97///UBx5lC6\nK9ozB6sXnjmY5eCqq67iueeeA+DkyZOcc845AFx++eU888wz1RyaWQ//EpyZmVXEwcHMzMp4Wcks\nB+eeey6/+MUvyuonT57Mz3/+8yqMyKzcQMtKDg5mOZD6fYCwf/jHaoZzDmZjbNq0aRXVm9UaBwez\nHEydOhVJPTOI0nHpfgezWudlJbMcOOdg9cDLSmZjbOLEvu8v7a/erNY4OJjloKmpiYaGBhoaGgB6\njgf7aUazWuFlJbMceFnJ6oG3spqNMW9ltXrgnIPZGJs8eXJF9Wa1xsHBLAezZs3qM+fg35C2ejHi\n4CBpgqQfSHosladJ2iJpv6QnJE3NtF0tqVPSPkkLMvXzJO2WdEBSe6Z+kqQNqc82STNHOl6zsbBn\nzx5Onz7d86tvpeM9e/ZUeWRmQzMaM4fbgb2Z8irgyYi4BHgKWA0g6VJgMTAXuAG4V28uzN4HLI+I\nOcAcSQtT/XLgWETMBtqBu0dhvGZmNogRBQdJFwK/BfxVpvomYH06Xg8sSsc3Ahsi4lREdAGdwHxJ\nFwBTImJnavdApk/2XJuAa0cyXjMzG5qRzhzuAT4FZLdfvDMijgJExCvA9FQ/AziUaXck1c0ADmfq\nD6e6M/pExGnghKTzRzhmMzMbxLBv15T028DRiNglqWWApqO5b6/f/YGtra09Nxg1NjbS3Nzc85OM\npR96d9nlsSz3p1bG5/L4KxcKBTo6OgAGvSFz2Pc5SPoz4GPAKeBcYArwMHA50BIRR9OS0daImCtp\nFRARsTb13wy0AQdLbVL9EuDqiLi11CYitktqAF6OiOm9huL7HKzm+D4Hqwe53OcQEXdGxMyIeDew\nBHgqIj4OfBNoTc2WAY+m48eAJWkH0sXALGBHWnrqljQ/JaiX9uqzLB3fTDHBbWZmOcvjKWCfBzZK\nuoXirGAxQETslbSR4s6m14EVmcv924AOYDLweERsTvXrgAcldQKvUgxCZmaWMz8+wywHXlayeuDH\nZ5iZWUUcHMzMrIyDg5mZlXFwMDOzMg4OZmZWxsHBzMzKODiYmVkZBwczMyvj4GBmZmUcHMzMrIyD\ng5mZlXFwMDOzMg4OZmZWxsHBzMzKODiYmVkZBwczMyvj4GBmZmUcHMzMrIyDg5mZlRl2cJB0oaSn\nJP1I0guSPpnqp0naImm/pCckTc30WS2pU9I+SQsy9fMk7ZZ0QFJ7pn6SpA2pzzZJM4c7XrPRIGlI\n/0Z6DrNqG8nM4RTwxxHxXuDXgNsk/SqwCngyIi4BngJWA0i6FFgMzAVuAO7Vm38F9wHLI2IOMEfS\nwlS/HDgWEbOBduDuEYzXbMQiouJ/w+lnVm3DDg4R8UpE7ErHrwH7gAuBm4D1qdl6YFE6vhHYEBGn\nIqIL6ATmS7oAmBIRO1O7BzJ9sufaBFw73PGamdnQjUrOQVIT0Ax8D3hnRByFYgABpqdmM4BDmW5H\nUt0M4HCm/nCqO6NPRJwGTkg6fzTGbGZm/Zs40hNIejvFq/rbI+I1Sb3nxKM5R+53Mba1tZWmpiYA\nGhsbaW5upqWlBYBCoQDgsstVKd9zzz0UCoWaGY/L47dcKBTo6OgA6Pm+7I9Gsr4paSLwLeDvIuIL\nqW4f0BIRR9OS0daImCtpFRARsTa12wy0AQdLbVL9EuDqiLi11CYitktqAF6OiOl9jCO8TmtmVhlJ\nRESfF90jXVb6KrC3FBiSx4DWdLwMeDRTvyTtQLoYmAXsSEtP3ZLmpwT10l59lqXjmykmuM3MLGfD\nnjlI+g3gu8ALFJeOArgT2AFsBN5FcVawOCJOpD6rKe5Aep3iMtSWVP9BoAOYDDweEben+nOAB4EP\nAK8CS1Iyu/dYPHOwmpVdUjKrJQPNHEa0rFQrHByslrW2FujoaKn2MMzKODiYVZEE/r+n1aI8cw5m\nZnYWcnAwy12h2gMwq5iDg5mZlXHOwSxnzjlYrRoo5zDiO6TN6tn558Px4/m/T94PWp02DY4dy/c9\nbHzxspKNa8ePF6/q8/y3dWsh9/cYiwBn44uDg5mZlXHOwca1syUfcLZ8Dhtbvs/BzMwq4oS0jWuB\nBngQ/OgoAC35vkV6Lr6nDjZ6PHOwcU3knCkuZqRzfw85MNgoc87BxrWzZa3+bPkcNracczAzs4o4\n52DjXt43qI1F1mHatFxPb+OQg4ONa2OxFOMlH6tHzjmY5czBwWqVcw5mZlaRuggOkq6X9I+SDkj6\ndLXHY1aZQrUHYFaxmg8OkiYAXwIWAu8FPirpV6s7KrNK7Kr2AMwqVvPBAZgPdEbEwYh4HdgA3FTl\nMZkN2dVXn6j2EMwqVg+7lWYAhzLlwxQDhtmY0zD3vUprKmrvDRZWbfUwczCrGRFR8b9ly5ZV3Mes\n2uph5nAEmJkpX5jqzjDcKzqzsbB+/fpqD8GsIjV/n4OkBmA/cC3wMrAD+GhE7KvqwMzMzmI1P3OI\niNOSVgJbKC6DrXNgMDPLV83PHMzMbOw5IW1mZmUcHKwmSTot6QeSnk///U+pviCpq1fbRyT9NB1f\nJOmFAc57taRv5jr48vc8V9JfS9ot6QVJ35X01kH6bJU0bwTv+ZXSzaKSVmfqp0q6dRjna5P0x8Md\nj9Wfms852Lj1fyOiry/HAE5I+vWIeFbSVOACzvyNzMHWSsd6LfV24JWI+BiApNnA63m+YUT8h0zx\nTuBz6XgasAK4L8/3t/rnmYPVqoH2Jm8APpqO/y3wtyN+M+naNEP5oaS/kvSWVP+fJW1PV/1fzrTf\nKunz6bV/lPQbA5z+X5DZfh0RnRHxeu9ZjqQ/kfSZTL+laea0W9LlqU2bpI40+/hnSR+RtDa1eTzt\n7uuZeUj6HHBu+mwPUgwS70nltantf5S0Q9IuSW2Z8fyppP2SvgtcMoL/ea0OOThYrSp9oZWWlW7O\nvPYU8JvpuVtLKAaLYZN0DnA/cHNEvB94C1BaevmLiLgyIt4HvFXSb2e6NkTElcAfAXcN8BZfBVZJ\n+t+SPitpVua1gWYx50bEB4Db0vhK3k3x14NuAv4a+E4a3y+A7PiIiNXAzyJiXkR8HFgFvJjKn5Z0\nHTA7IuYDHwAul3RVWtJaDLwvnfOKAcZpZyEvK1mt+lk/y0oAp4BnKAaGyRHxkkZ2F+QlwD9FxP9J\n5fUUl16+CFwr6VPAWykuyewBvp3alWYs3wcu6u/kEfFDSRcDC4DrgB2Sfo3il/lA/ib1/wdJUySd\nl+r/LiLeSLOOCRGxJdW/ADQN5QNnLACuk/QDirO1twGzgfOAhyPiJHBS0mMVntfqnIOD1auvAw8D\npWWYkeYRyoJLmlH8D2BeRPw4LblMzjQ5mf57mkH+liLiZ8AjwCOS3gB+C9gINGSaTe7drZ/yyXTO\nkJTNXbzRzzgGCpwCPhcRf3lGpXT7AH1sHPCyktWqAWcCEfEPwJ/x5pJStv1gs4jer+8HLpL07lT+\nOMUfYZhM8Qv5VUlvB353OOOV9OuSGtPxJOBSoAs4CvyypGkpEP2bXl1/L/W5CuiOiJ9W8r4Z/09S\nKWj8FJiSee0J4BZJb0vv9SuSfhn4LrBI0jmSpgC/M4T3sbOIZw5WqyZnljoC2BwRd5K5mo6IP8+0\nr2S30jWSXsqc+2bgE8CmlNDdCfzPlDT+S+BHvPnolv7eY6D3fA9wX1r5mgB8OyIeBpD0X9L7HQay\nd/4H8Iv0v8HENL6+9Pe+2fqvALslfT8iPi7pWUm7KS5PfVrSXGBbGt9PgY9FxPOSNgK7KQaxHb3f\nwM5uvkPazMzKeFnJzMzKeFnJzkqSFgBreXN5RRR3JP27s+k9zfLiZSUzMyvjZSUzMyvj4GBmZmUc\nHMzMrIyDg5mZlXFwMDOzMv8f+6TPcTcvUBIAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x10b270a10>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data.boxplot(column=['EMI_Loan_Submitted'],return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>EMI_Loan_Submitted_Missing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6762.90</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6978.92</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>30824.65</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10883.38</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   EMI_Loan_Submitted  EMI_Loan_Submitted_Missing\n",
       "0                 NaN                           1\n",
       "1             6762.90                           0\n",
       "2                 NaN                           1\n",
       "3                 NaN                           1\n",
       "4                 NaN                           1\n",
       "5             6978.92                           0\n",
       "6                 NaN                           1\n",
       "7                 NaN                           1\n",
       "8            30824.65                           0\n",
       "9            10883.38                           0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Majority values missing so I'll create a new variable stating whether this is missing or note:\n",
    "data['EMI_Loan_Submitted_Missing'] = data['EMI_Loan_Submitted'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "data[['EMI_Loan_Submitted','EMI_Loan_Submitted_Missing']].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#drop original vaiables:\n",
    "data.drop('EMI_Loan_Submitted',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Employer Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "57193"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data['Employer_Name'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#I'll drop the variable because too many unique values. Another option could be to categorize them manually\n",
    "data.drop('Employer_Name',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Existing EMI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x109d08c50>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXEAAAEHCAYAAABY/HZ4AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAEAxJREFUeJzt3H2MZXV9x/H3B1axtbLjQyIVhGlVQqvilBRCGttepJVF\nG8VaK0tjOlSbTStG2j8EExvZhMRSm3YitJJVdGsThNSn4NNKQ/dirA/QygrW5WHVIqzEapW1i2i3\n67d/zJnhMjsPd3Zn955z9/1KNtzfOb8584PsfDjzOQ+pKiRJ3XTMqBcgSTp4hrgkdZghLkkdZohL\nUocZ4pLUYYa4JHXYEQ/xJNcl+U6SO4eY+zdJ7kjy5ST3JPn+kVijJHVFjvR94kleDOwFPlBVp6/i\n6y4BpqrqDYdtcZLUMUf8TLyqPgf8YHBbkl9M8ukktye5Ncmpi3zpRuCDR2SRktQR60a9gMYWYFNV\nfT3JWcC7gXPndiY5GZgE/mU0y5Okdhp5iCd5MvBrwD8lSbP5CQumXQh8qHxHgCQ9zshDnNlK5wdV\ndcYycy4E/vQIrUeSOmPFTnyYu0mSvCvJfUl2JJka4vum+UNV/Q/wzSS/N3C80wc+nwZMVNUXhziu\nJB1Vhrmw+X7gvKV2JjkfeE5VPQ/YBFy73MGSXA98Hjg1ybeSXAz8AfD65n8CXwVeMfAlrwVuGGKd\nknTUGeoWwySnAB9f7JbAJNcC26vqxma8E+hV1XfWerGSpMdbi1sMTwQeGBjvbrZJkg4zH7uXpA5b\ni7tTdgPPHhif1Gw7QBJvEZSkg1BVWWz7sCE+fzfJIm4C3gjcmORs4OHl+nBv9VZb9Xo9+v3+qJch\nHeCxR2gOtGKIN3eT9ICnJ/kW8HbgiUBV1Zaq+lSSlyXZBTwCXLwmq5aOsIcffnjUS5BWbcUQr6qL\nhphzydosRxqdE044YdRLkFbNC5tS4/LLLx/1EqRVO6Kvok3i608kaZWSLHlh0zNxqeFFTXWRIS5J\nHWadIkktZ50iSWPKEJcaduLqIkNckjrMTlySWs5OXJLGlCEuNezE1UWGuCR1mJ24JLWcnbgkjSlD\nXGrYiauLDHFJ6jA7cUlqOTtxSRpThrjUsBNXFxniktRhduKS1HJ24pI0pgxxqWEnri4yxCWpw+zE\nJanl7MQlaUwZ4lLDTlxdZIhLUofZiUtSy9mJS9KYMsSlhp24usgQl6QOsxOXpJazE5ekMWWISw07\ncXWRIS5JHTZUiCfZkOTuJPcmuWyR/ccnuSnJjiR3JZle85VKh1mv1xv1EqRVW/HCZpJjgHuBc4Fv\nA7cDF1bV3QNz3gocX1VvTfIM4B7gmVX1fwuO5YVNSVqlQ72weRZwX1XdX1X7gBuAVy6YU8BTms9P\nAf57YYBLbWcnri4aJsRPBB4YGD/YbBt0DfDLSb4NfAV489osT5K0nHVrdJzzgDuq6iVJngP8c5LT\nq2rvwonT09NMTk4CMDExwdTU1HwXOXcm5NjxKMZz29qyHsdH77jf77N161aA+bxcyjCd+NnAFVW1\noRlfDlRVXTUw5xPAO6rqX5vxLcBlVfVvC45lJy5Jq3SonfjtwHOTnJLkicCFwE0L5twP/FbzzZ4J\nnAp84+CXLB15c2dCUpesWKdU1f4klwA3Mxv611XVziSbZnfXFuBKYGuSO5sve0tVff+wrVqSBPju\nFElqPd+dIkljyhCXGnbi6iJDXJI6zE5cklrOTlySxpQhLjXsxNVFhrgkdZiduCS1nJ24JI0pQ1xq\n2ImriwxxSeowO3FJajk7cUkaU4a41LATVxcZ4pLUYXbiktRyduKSNKYMcalhJ64uMsQlqcPsxCWp\n5ezEJWlMGeJSw05cXWSIS1KH2YlLUsvZiUvSmDLEpYaduLrIEJekDrMTl6SWsxOXpDFliEsNO3F1\nkSEuSR1mJy5JLWcnLkljyhCXGnbi6iJDXJI6bKhOPMkGYIbZ0L+uqq5aZE4P+FvgCcB3q+qcRebY\niUvSKi3Xia8Y4kmOAe4FzgW+DdwOXFhVdw/MWQ98HnhpVe1O8oyq+t4ixzLEJWmVDvXC5lnAfVV1\nf1XtA24AXrlgzkXAh6tqN8BiAS61nZ24umiYED8ReGBg/GCzbdCpwNOSbE9ye5LXrdUCJUlLW7eG\nxzkDeAnwZOALSb5QVbvW6PjSYdfr9Ua9BGnVhgnx3cDJA+OTmm2DHgS+V1U/Bn6c5LPAi4ADQnx6\neprJyUkAJiYmmJqamv/hmft11rFjx46P5nG/32fr1q0A83m5lGEubB4L3MPshc2HgNuAjVW1c2DO\nacDVwAbgOOBLwGur6msLjuWFTbVWv9+f/4GS2mS5C5srnolX1f4klwA389gthjuTbJrdXVuq6u4k\nnwHuBPYDWxYGuCRp7fnuFElqOd+dIkljyhCXGnMXlqQuMcQlqcPsxCWp5ezEJWlMGeJSY2ZmZtRL\nkFbNEJcaO3bsGPUSpFUzxKXGSo83S220Vi/Akjqp3+/P31q4efPm+e29Xs9H8NUJ3p0iNaanp+df\nOiS1iXenSNKYMsSlxvT09KiXIK2adYoktZx1ijQE352iLjLEJanDrFMkqeWsUyRpTBniUsNOXF1k\niEtSh9mJS1LL2YlL0pgyxKWGnbi6yBCXpA6zE5eklrMTl6QxZYhLDTtxdZEhLkkdZicuSS1nJy5J\nY8oQlxp24uoiQ1ySOsxOXJJazk5cksaUIS417MTVRYa4JHWYnbgktdwhd+JJNiS5O8m9SS5bZt6Z\nSfYl+d2DXawkaXgrhniSY4BrgPOA5wMbk5y2xLy/BD6z1ouUjgQ7cXXRMGfiZwH3VdX9VbUPuAF4\n5SLz3gR8CPivNVyfJGkZw4T4icADA+MHm23zkjwLuKCq3g0s2ttIbdfr9Ua9BGnV1urulBlgsCs3\nyCXpCFg3xJzdwMkD45OabYN+FbghSYBnAOcn2VdVNy082PT0NJOTkwBMTEwwNTU1fwY010k6djyK\n8czMjH8fHbdi3O/32bp1K8B8Xi5lxVsMkxwL3AOcCzwE3AZsrKqdS8x/P/DxqvrIIvu8xVCt1e/3\n53+gpDZZ7hbDFc/Eq2p/kkuAm5mtX66rqp1JNs3uri0Lv+SQVyyNgAGuLvJhH0lqOV+AJQ1hrpOU\nusQQl6QOs06RpJazTpGkMWWISw07cXWRIS5JHWYnLkktZycuSWPKEJcaduLqIkNckjrMTlySWs5O\nXJLGlCEuNezE1UWGuCR1mJ24JLWcnbgkjSlDXGrYiauLDHFJ6jA7cUlqOTtxSRpThrjUsBNXFxni\nktRhduKS1HJ24pI0pgxxqWEnri4yxCWpw+zEJanl7MQlaUwZ4lLDTlxdZIhLUofZiUtSy9mJS9KY\nMsSlhp24usgQl6QOsxOXpJazE5ekMTVUiCfZkOTuJPcmuWyR/Rcl+Urz53NJXrj2S5UOLztxddGK\nIZ7kGOAa4Dzg+cDGJKctmPYN4Deq6kXAlcB71nqhkqQDrdiJJzkbeHtVnd+MLweqqq5aYv4EcFdV\nPXuRfXbikrRKh9qJnwg8MDB+sNm2lDcAnx5+eZKkg7VuLQ+W5BzgYuDFS82Znp5mcnISgImJCaam\npuj1esBjnaRjx6MYz8zM+PfRcSvG/X6frVu3Aszn5VKGrVOuqKoNzXjROiXJ6cCHgQ1V9fUljmWd\notbq9/vzP1BSmyxXpwwT4scC9wDnAg8BtwEbq2rnwJyTgVuA11XVF5c5liEuSau0XIivWKdU1f4k\nlwA3M9uhX1dVO5Nsmt1dW4C/AJ4G/H2SAPuq6qy1+1eQJC3GJzalhnWK2sonNiVpTHkmLkkt55m4\nJI0pQ1xqzN2nK3WJIS5JHWYnLkktZycuSWPKEJcaduLqIkNckjrMTlySWs5OXJLGlCEuNezE1UWG\nuCR1mJ24JLWcnbgkjSlDXGrYiauLDHFJ6jA7cUlqOTtxSRpThrjUsBNXFxniktRhduKS1HJ24pI0\npgxxqTEzMzPqJUirZohLjR07dox6CdKqGeJSY3JyctRLkFZt3agXII1Sv9+fv7Vw8+bN89t7vR69\nXm80i5JWwbtTpMaGDRvYtm3bqJchHWC5u1MMcamxfv169uzZM+plSAfwFkNpCI8++uiolyCtmp24\njmqDnfi+ffu44oorADtxdYd1io5qr3rVq9i+fTsAe/bsYf369QCcc845fPSjHx3l0qR5duLSEJof\nlFEvQzrAciFunaKj2szMDB/72Mfmx3MVygUXXMCll146olVJwzPEdVS79dZbH/ek5tznpz71qYa4\nOmGou1OSbEhyd5J7k1y2xJx3JbkvyY4kU2u7TOnw2LVrF3v37mXv3r0A85937do14pVJw1kxxJMc\nA1wDnAc8H9iY5LQFc84HnlNVzwM2AdcehrVKa279+vWsW7eOdetmfymd+zx3gVNquxUvbCY5G3h7\nVZ3fjC8HqqquGphzLbC9qm5sxjuBXlV9Z8GxvLCpVkkWvVYE4EVOtcahPuxzIvDAwPjBZttyc3Yv\nMkeStMZ8YlOSOmyYu1N2AycPjE9qti2c8+wV5gDL//oqtYl/V9UFw4T47cBzk5wCPARcCGxcMOcm\n4I3AjU2H/vDCPhxYstORJB2cFUO8qvYnuQS4mdn65bqq2plk0+zu2lJVn0rysiS7gEeAiw/vsiVJ\ncIQfu5ckrS0vbEpShxniapUk+5N8OckdzT/fssL8TyQ5fpn9b07ypGHnH8R6fzPJwwvW/JJm30+T\nfGBg7rFJvpvkpmb8h0muXqu16Ojku1PUNo9U1RnDTq6q31lhyqXAPwI/HnL+wfhsVb1ike2PAC9I\nclxV/QT4bR7/PAWAfaYOiWfiapsD7mBKcnzz7p7nNePrk7y++fzNJE9L8rPNWfYdSe5M8pokbwKe\nBWxPcsuC+ack+VqSLUm+mmRbkuOaOWcm+UpzVv1XSe5a7ZoHfAp4efN5I/DBVf3XkFZgiKttfmZB\nNfGaqvohs7ew/kOS1wITVXVdM3/uTHYDsLuqfqWqTge2VdXVzD6v0KuqcxfMB3gucHVVvQDYA7y6\n2f4+4I+b3wj2s/LZ8q8vWPMvDHyvG5h939BxwOnAl1b/n0RamnWK2uZHi9UpVXVLkt8H/g544cCu\nubPgu4C/TvIO4JNV9bmB/VlkPsA3q2ruLPvfgckk64Gfq6rbmu3X89iZ9FKWqlOoqq8mmWT2LPyT\nLH/WLq2aZ+LqhMw+PvlLzPbMT1+4v6ruA85gNsyvTPK2IQ77k4HP+3nspGatg/Ym4J1YpegwMMTV\nNksF6J8DXwMuAt6f5NjHfVHy88CjVXU9s4E5dzb/Q2Cpu1EO+F5VtQf4YZIzm00XHsKa57a/D9hc\nVf8xxLGkVbFOUds8KcmXmQ3AArYBW4E/As6sqh8luRV4G7CZx/rqFwLvTPJT4H+BP2m2vwfYlmR3\n04sP9ttLdd1vAN6bZD9wK7N9+XJevGDNV1bVR+aOX1W7mX0nv7TmfGJTWiDJk6vqkebzZcAJVfVn\nI16WtCjPxKUDvTzJW5n9+fhPYHqkq5GW4Zm4NIQkLwWu4rEKJsA3qurVS3+VdPgZ4pLUYd6dIkkd\nZohLUocZ4pLUYYa4JHWYIS5JHfb/fwoShbIx6AkAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x109f8a9d0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data.boxplot(column='Existing_EMI',return_type='axes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      124626.000000\n",
       "mean         3636.342492\n",
       "std         33691.240042\n",
       "min             0.000000\n",
       "25%             0.000000\n",
       "50%             0.000000\n",
       "75%          3500.000000\n",
       "max      10000000.000000\n",
       "Name: Existing_EMI, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Existing_EMI'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Impute by median (0) because just 111 missing:\n",
    "data['Existing_EMI'].fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Interest Rate:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Interest_Rate  Interest_Rate_Missing\n",
      "0            NaN                      1\n",
      "1          13.25                      0\n",
      "2            NaN                      1\n",
      "3            NaN                      1\n",
      "4            NaN                      1\n",
      "5          13.99                      0\n",
      "6            NaN                      1\n",
      "7            NaN                      1\n",
      "8          14.85                      0\n",
      "9          18.25                      0\n"
     ]
    }
   ],
   "source": [
    "#Majority values missing so I'll create a new variable stating whether this is missing or note:\n",
    "data['Interest_Rate_Missing'] = data['Interest_Rate'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "print data[['Interest_Rate','Interest_Rate_Missing']].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.drop('Interest_Rate',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lead Creation Date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Drop this variable because doesn't appear to affect much intuitively\n",
    "data.drop('Lead_Creation_Date',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loan Amount and Tenure applied:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Impute with median because only 111 missing:\n",
    "data['Loan_Amount_Applied'].fillna(data['Loan_Amount_Applied'].median(),inplace=True)\n",
    "data['Loan_Tenure_Applied'].fillna(data['Loan_Tenure_Applied'].median(),inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loan Amount and Tenure selected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#High proportion missing so create a new var whether present or not\n",
    "data['Loan_Amount_Submitted_Missing'] = data['Loan_Amount_Submitted'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "data['Loan_Tenure_Submitted_Missing'] = data['Loan_Tenure_Submitted'].apply(lambda x: 1 if pd.isnull(x) else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Remove old vars\n",
    "data.drop(['Loan_Amount_Submitted','Loan_Tenure_Submitted'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove logged-in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.drop('LoggedIn',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Remove salary account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Salary account has mnay banks which have to be manually grouped\n",
    "data.drop('Salary_Account',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processing_Fee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#High proportion missing so create a new var whether present or not\n",
    "data['Processing_Fee_Missing'] = data['Processing_Fee'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "#drop old\n",
    "data.drop('Processing_Fee',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S122      55249\n",
       "S133      42900\n",
       "others    26588\n",
       "Name: Source, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Source'] = data['Source'].apply(lambda x: 'others' if x not in ['S122','S133'] else x)\n",
    "data['Source'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Final Data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Device_Type                          0\n",
       "Disbursed                        37717\n",
       "Existing_EMI                         0\n",
       "Filled_Form                          0\n",
       "Gender                               0\n",
       "ID                                   0\n",
       "Loan_Amount_Applied                  0\n",
       "Loan_Tenure_Applied                  0\n",
       "Mobile_Verified                      0\n",
       "Monthly_Income                       0\n",
       "Source                               0\n",
       "Var1                                 0\n",
       "Var2                                 0\n",
       "Var4                                 0\n",
       "Var5                                 0\n",
       "source                               0\n",
       "Age                                  0\n",
       "EMI_Loan_Submitted_Missing           0\n",
       "Interest_Rate_Missing                0\n",
       "Loan_Amount_Submitted_Missing        0\n",
       "Loan_Tenure_Submitted_Missing        0\n",
       "Processing_Fee_Missing               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Device_Type                       object\n",
       "Disbursed                        float64\n",
       "Existing_EMI                     float64\n",
       "Filled_Form                       object\n",
       "Gender                            object\n",
       "ID                                object\n",
       "Loan_Amount_Applied              float64\n",
       "Loan_Tenure_Applied              float64\n",
       "Mobile_Verified                   object\n",
       "Monthly_Income                     int64\n",
       "Source                            object\n",
       "Var1                              object\n",
       "Var2                              object\n",
       "Var4                               int64\n",
       "Var5                               int64\n",
       "source                            object\n",
       "Age                                int64\n",
       "EMI_Loan_Submitted_Missing         int64\n",
       "Interest_Rate_Missing              int64\n",
       "Loan_Amount_Submitted_Missing      int64\n",
       "Loan_Tenure_Submitted_Missing      int64\n",
       "Processing_Fee_Missing             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Numerical Coding:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "le = LabelEncoder()\n",
    "var_to_encode = ['Device_Type','Filled_Form','Gender','Var1','Var2','Mobile_Verified','Source']\n",
    "for col in var_to_encode:\n",
    "    data[col] = le.fit_transform(data[col])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### One-Hot Coding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'Disbursed', u'Existing_EMI', u'ID', u'Loan_Amount_Applied',\n",
       "       u'Loan_Tenure_Applied', u'Monthly_Income', u'Var4', u'Var5', u'source',\n",
       "       u'Age', u'EMI_Loan_Submitted_Missing', u'Interest_Rate_Missing',\n",
       "       u'Loan_Amount_Submitted_Missing', u'Loan_Tenure_Submitted_Missing',\n",
       "       u'Processing_Fee_Missing', u'Device_Type_0', u'Device_Type_1',\n",
       "       u'Filled_Form_0', u'Filled_Form_1', u'Gender_0', u'Gender_1', u'Var1_0',\n",
       "       u'Var1_1', u'Var1_2', u'Var1_3', u'Var1_4', u'Var1_5', u'Var1_6',\n",
       "       u'Var1_7', u'Var1_8', u'Var1_9', u'Var1_10', u'Var1_11', u'Var1_12',\n",
       "       u'Var1_13', u'Var1_14', u'Var1_15', u'Var1_16', u'Var1_17', u'Var1_18',\n",
       "       u'Var2_0', u'Var2_1', u'Var2_2', u'Var2_3', u'Var2_4', u'Var2_5',\n",
       "       u'Var2_6', u'Mobile_Verified_0', u'Mobile_Verified_1', u'Source_0',\n",
       "       u'Source_1', u'Source_2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.get_dummies(data, columns=var_to_encode)\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Separate train & test:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train = data.loc[data['source']=='train']\n",
    "test = data.loc[data['source']=='test']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/aarshay/anaconda/lib/python2.7/site-packages/ipykernel/__main__.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  if __name__ == '__main__':\n",
      "/Users/aarshay/anaconda/lib/python2.7/site-packages/ipykernel/__main__.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "train.drop('source',axis=1,inplace=True)\n",
    "test.drop(['source','Disbursed'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train.to_csv('train_modified.csv',index=False)\n",
    "test.to_csv('test_modified.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
